<?php  if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class group_model extends CI_Model{

	function __construct()
    {
        parent::__construct();
    }
	
	function get_file_by_group_id($group_id,$only_non_template=false)
	{
		$this->db->select('*');
		$this->db->from('file');
		if($only_non_template)
			$this->db->where('is_template ',0);
		$this->db->where('group_id',$group_id);
		$query  = $this->db->get();
		return $query->result();
	}
	
	function get_group_by_user_id($user_id){
		if($this->config->item('use_infinitec_db', 'ion_auth'))
		{
			$user = $this->ion_auth->user()->row();
			$serverName = $this->config->item('awb_manager_ip'); //serverName\instanceName
				
			$connectionInfo = array( "Database"=>$this->config->item('awb_manager_db'), "UID"=>$this->config->item('awb_manager_db_user'), "PWD"=>$this->config->item('awb_manager_db_password'),"CharacterSet"=>"UTF-8");
			$conn = sqlsrv_connect( $serverName, $connectionInfo);
			if(!$conn)
			{
				show_error('Cannot connect to AWB Manager Server');
				return false;
			}
			
			if($user->role =="STUDENT")
			{
				

				$sql = "SELECT * FROM AWBSTUDENT JOIN AWBGROUP ON (AWBGROUP.AWBGROUP_ID = AWBSTUDENT.AWBGROUP_ID) WHERE STUDENT_ID = '$user_id' "; 
				$params = array();

				$stmt = sqlsrv_query( $conn, $sql, $params);
				if( $stmt === false ) {
					 die( print_r( sqlsrv_errors(), true));
				}
				$group_data =array();
				while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
					array_push($group_data,$row);
				}
				foreach($group_data as $key=>$single_group_data)
				{	
					$ws_id = $single_group_data['WS_ID'];
					$sql = "SELECT * FROM WORKSPACE WHERE WS_ID = '$ws_id' "; 
					$params = array();

					$stmt = sqlsrv_query( $conn, $sql, $params);
					if( $stmt ) {
						while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
							$group_data[$key]['WS_NAME'] = $row['WS_NAME'];
						}
					}
			
				}
			}
			else
			{

				$sql = "SELECT WS_ID FROM WORKSPACE WHERE WORKSPACE.TEACHER_ID = '$user_id' "; 
				$params = array();

				$stmt = sqlsrv_query( $conn, $sql, $params);
				if( $stmt === false ) {
					 die( print_r( sqlsrv_errors(), true));
				}
				$ws_data =array();
				while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
					array_push($ws_data,$row['WS_ID']);
				}
				
				$query  =$this->db->query("SELECT * FROM awbgroup WHERE ws_id in (".implode(',',$ws_data).") ");
				$group_data =  $query->result();
				foreach($group_data as $key=>$single_group_data)
				{	
					$group_data[$key] = (array)$single_group_data;
					$ws_id = $group_data[$key]['WS_ID'];
					$query  =$this->db->query("SELECT * FROM AWBWORKSPACE WHERE WS_ID = '$ws_id' ");
					$result =  $query->row();
					$result = (array)$result;
					$group_data[$key]['WS_NAME'] = $result['WS_NAME'];
				}
			}
		}
		else
		{	
			$user = $this->ion_auth->user()->row();
			if($user->role =="STUDENT")
			{
				$query  =$this->db->query("SELECT * FROM AWBSTUDENT JOIN AWBGROUP ON (AWBGROUP.AWBGROUP_ID = AWBSTUDENT.AWBGROUP_ID) WHERE STUDENT_ID = '$user_id' ");
				$group_data =  $query->result();
				foreach($group_data as $key=>$single_group_data)
				{	
					$group_data[$key] = (array)$single_group_data;
					$ws_id = $group_data[$key]['WS_ID'];
					$query  =$this->db->query("SELECT * FROM AWBWORKSPACE WHERE WS_ID = '$ws_id' ");
					$result =  $query->row();
					$result = (array)$result;
					$group_data[$key]['WS_NAME'] = $result['WS_NAME'];

			
				}
			}
			else
			{
			//JOIN AWBGROUP ON (AWBGROUP.WS_ID = awbworkspace.WS_ID) 
				$query  =$this->db->query("SELECT WS_ID FROM awbworkspace WHERE awbworkspace.TEACHER_ID = '$user_id' ");
				$ws_data =  $query->result();
				$ws_id_array = array();
				foreach($ws_data as $key=>$single_ws_data)
				{
					array_push($ws_id_array,$single_ws_data->WS_ID);
				}
				$query  =$this->db->query("SELECT * FROM awbgroup WHERE ws_id in (".implode(',',$ws_id_array).") ");
				$group_data =  $query->result();
				foreach($group_data as $key=>$single_group_data)
				{	
					$group_data[$key] = (array)$single_group_data;
					$ws_id = $group_data[$key]['WS_ID'];
					$query  =$this->db->query("SELECT * FROM AWBWORKSPACE WHERE WS_ID = '$ws_id' ");
					$result =  $query->row();
					$result = (array)$result;
					$group_data[$key]['WS_NAME'] = $result['WS_NAME'];
				}
			}
		}
		return $group_data;
	}
}

?>